package com.db.Dao.impl;

import com.db.Dao.PersonDao;
import com.db.entity.Person;
import com.db.util.DButils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class PersonDaoImpl implements PersonDao {
    @Override
    public List<Person> searchAll(Integer userid) {
        Connection conn = null;

        List<Person> list = new ArrayList<>();

        try{
            conn = DButils.getConnection();
            String sql = "select person_id as id,person_name as name ,birthday,sex,mobile,address,face from t_person where userid=?";
            QueryRunner runner = new QueryRunner();
            list = runner.query(conn,sql,new BeanListHandler<Person>(Person.class),userid);


        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);

        }

        return list;
    }

    @Override
    public Person searchByNameMobile(Person person,Integer userid){
        Connection conn = null;

        Person person1 = null;

        try{
            conn = DButils.getConnection();

            String sql = "select person_id as id ,person_name as name,birthday,sex,mobile,address,face from t_person " +
                    " where person_name = ? and mobile = ? and userid = ?";
            QueryRunner runner = new QueryRunner();
            person1 = runner.query(conn,sql,new BeanHandler<Person>(Person.class),person.getName(),person.getMobile(),userid);


        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);

        }


        return person1;
    }

    @Override
    public void addPerson(Person person){
        Connection conn = null;

        try{
            conn = DButils.getConnection();
            String sql = "insert into  t_person  values (null,?,?,?,?,?,?,?)";

            QueryRunner runner = new QueryRunner();
            Date date = new Date(person.getBirthday().getTime());
            runner.update(conn,sql,person.getName(),date,person.getSex(),person.getMobile(),person.getAddress(),person.getFace(),person.getUserid());


        }catch( Exception e ){
            e.printStackTrace();
            throw new RuntimeException(e);

        }

    }

    @Override
    public void deletePersonById(Integer personId) {
        Connection conn = null;


        try{
            conn = DButils.getConnection();
            String sql = "delete from t_person where person_id = ?";

            QueryRunner runner = new QueryRunner();
            runner.update(conn,sql,personId);

        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);

        }

    }

    @Override
    public Person selectPersonById(Integer personId,Integer userid){
        Connection conn = null;

        Person person = null;

        try{
            conn = DButils.getConnection();
            String sql = "select person_id as id,person_name as name,birthday,sex,mobile,address,face from t_person where person_id = ? and userid = ?";
            QueryRunner runner = new QueryRunner();
            person = runner.query(conn,sql,new BeanHandler<Person>(Person.class),personId,userid);

        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);

        }

        return person;
    }

    @Override
    public void updatePerson(Person person) {
        Connection conn = null;


        try{
            conn = DButils.getConnection();
            Date date = new Date(person.getBirthday().getTime());

            String sql = "update t_person set person_name = ?,birthday = ?,sex = ?,mobile = ?, address = ?,face = ? " +
                    "where person_id = ?";

            QueryRunner runner = new QueryRunner();
            runner.update(conn,sql,person.getName(),date,person.getSex(),person.getMobile(),person.getAddress(),person.getFace(),person.getId());



        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);

        }
    }

    @Override
    public List<Person> searchlimit(Integer pageIndex,Integer userid) {
        Connection conn = null;

        List<Person> list = new ArrayList<>();

        try{
            conn = DButils.getConnection();
            //String sql = "select person_id as id,person_name as name,birthday,sex,mobile,address,face from t_person where userid = ? order by person_id limit ?,?";
            String sql = "select person_id as id,person_name as name,birthday,sex,mobile,address,face from t_person where userid = ?  order by person_id limit ?,? ;";

            QueryRunner runner = new QueryRunner();
            list = runner.query(conn,sql,new BeanListHandler<Person>(Person.class),userid,(pageIndex-1)*PAGE_SIZE,PAGE_SIZE);



        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);

        }
        return list;
    }

    @Override
    public List<Person> searchByName(Integer pageIndex, String personName,Integer userid)  {
        Connection conn = null;

        List<Person> list = null;

        try{
            conn = DButils.getConnection();
            String sql = "select person_id as id,person_name as name ,birthday,sex,mobile,address,face from t_person where userid = ? and person_name like ? order by person_id limit ?,?";

            QueryRunner runner = new QueryRunner();
            list = runner.query(conn,sql,new BeanListHandler<Person>(Person.class),userid,"%"+personName+"%",(pageIndex-1)*PAGE_SIZE,PAGE_SIZE);

        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);

        }
        return list;
    }

    @Override
    public List<Person> searchByMobile(Integer pageIndex, String Mobile,Integer userid)  {
        Connection conn = null;

        List<Person> list = new ArrayList<>();

        try{
            conn = DButils.getConnection();
            String sql = "select person_id as id ,person_name as name,birthday,sex,mobile,address,face from t_person where userid = ? and mobile like ? order by person_id limit ?,?";
            QueryRunner runner = new QueryRunner();
            list = runner.query(conn,sql,new BeanListHandler<Person>(Person.class),userid,"%"+Mobile+"%",(pageIndex-1)*PAGE_SIZE,PAGE_SIZE);

        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);

        }
        return list;
    }

    @Override
    public List<Person> searchByAddress(Integer pageIndex, String Address,Integer userid) {
        Connection conn = null;

        List<Person> list = new ArrayList<>();

        try{
            conn = DButils.getConnection();
            String sql = "select person_id as id,person_name as name,birthday,sex,mobile,address,face from t_person where userid = ? and address like ? order by person_id limit ?,?";

            QueryRunner runner = new QueryRunner();
            list = runner.query(conn,sql,new BeanListHandler<Person>(Person.class),userid,"%"+Address+"%",(pageIndex-1)*PAGE_SIZE,PAGE_SIZE);


        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);

        }

        return list;
    }

    @Override
    public List<Person> searchByNameNo(String personName,Integer userid){
        Connection conn = null;

        List<Person> list = new ArrayList<>();

        try{
            conn = DButils.getConnection();
            String sql = "select person_id as id,person_name as name,birthday,sex,mobile,address,face from t_person where userid = ? and person_name like ?  ";

            QueryRunner runner = new QueryRunner();
            list = runner.query(conn,sql,new BeanListHandler<Person>(Person.class),userid,"%"+personName+"%");


        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);

        }

        return list;
    }

    @Override
    public List<Person> searchByMobileNo(String Mobile,Integer userid){
        Connection conn = null;

        List<Person> list = new ArrayList<>();

        try{
            conn = DButils.getConnection();
            String sql = "select person_id as id,person_name as name,birthday,sex,mobile,address,face from t_person where userid = ? and mobile like ?  ";

            QueryRunner runner = new QueryRunner();
            list = runner.query(conn,sql,new BeanListHandler<Person>(Person.class),userid,"%"+Mobile+"%");



        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);

        }

        return list;
    }

    @Override
    public List<Person> searchByAddressNo(String Address,Integer userid){
        Connection conn = null;

        List<Person> list = new ArrayList<>();

        try{
            conn = DButils.getConnection();
            String sql = "select person_id as id,person_name as name,birthday,sex,mobile,address,face from t_person where userid = ? and address like ?  ";

            QueryRunner runner = new QueryRunner();
            list = runner.query(conn,sql,new BeanListHandler<Person>(Person.class),userid,"%"+Address+"%");


        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);

        }

        return list;
    }



}
